package com.zhi.dao;

import com.zhi.entity.GradeTeacher;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * 院系班级对象dao层,与数据库交互
 */
public interface GradeTeacherRepository extends JpaRepository<GradeTeacher, Integer>{


	@Query("SELECT a.id,a.username,a.password,a.userType FROM User a")
	public List<Object[]> getEntityListWithCodeName();
	
	@Query(value = "select a.id," +
			"(select CONCAT(b.faculty_Id,'--',b.grade_Name) from Grade b where b.grade_Id=a.grade_Id) grade_Id," +
			"(select CONCAT(c.name,'--',c.class_name) from teacher c where c.teacher_id=a.teacher_id) teacher_id from grade_teacher a",
			countQuery ="select count(*) from grade_teacher",
			nativeQuery = true)
	public Page<GradeTeacher> getEntityListWithCodeName(Pageable pageable);

	/**
	 * 根据gradeId删除
	 * Query注解执行删除、修改时，需要加上Modifying、Transactional注解
	 * @param gradeId
	 */
	@Modifying
	@Transactional
	@Query(value = "delete from grade_teacher where grade_id =?1 ",nativeQuery = true)
	public void deleteByGradeId(String gradeId);

}
